BEGIN;

DROP VIEW win_observation_textual_data_view;

CREATE OR REPLACE VIEW win_observation_textual_data_view AS
SELECT a.*, win_scientific_name.s_name, win_scientific_name.s_author from (
 SELECT win_observations.id, win_observations.s_kingdom, win_observations.s_phylum, win_observations.s_class, win_observations.s_order, win_observations.s_family, win_observations.s_genus, win_observations.s_species, win_observations.s_scientific_name, win_observations.waterbody_type, win_observations.waterbody_name, win_observations.comments, win_observations.record_language, win_observations.basis_of_record, win_observations.taxanomic_status, win_observations.nomenclature_type_status, win_observations.nativeness, win_observations.iucn_threat_category, win_observer.fname, win_observer.lname, win_observer.affiliation, win_observer.email, win_observation_location.country, win_observation_location.region, win_observation_location.district, win_observation_location.city, win_observation_location.locality
   FROM win_observations, win_observer, win_observation_location
  WHERE win_observations.observer_id = win_observer.id AND win_observations.id = win_observation_location.observation_id
  ORDER BY win_observations.id) AS a
  left join win_scientific_name ON a.id = win_scientific_name.observation_id;

ALTER TABLE win_common_names ADD COLUMN common_name_english VARCHAR;


--DROP VIEW win_point_observations_view;

CREATE OR REPLACE VIEW win_point_observations_view AS
 SELECT win_observations.id, win_observations.topology
   FROM win_observations
  WHERE (win_observations.id IN ( SELECT DISTINCT win_validation.observation_id
           FROM win_validation
          WHERE win_validation.status = 1
          ORDER BY win_validation.observation_id)) AND split_part(astext(win_observations.topology), '('::text, 1) = 'POINT'::text;


--DROP VIEW win_polygon_observations_view;

CREATE OR REPLACE VIEW win_polygon_observations_view AS
 SELECT win_observations.id, win_observations.topology
   FROM win_observations
  WHERE (win_observations.id IN ( SELECT DISTINCT win_validation.observation_id
           FROM win_validation
          WHERE win_validation.status = 1
          ORDER BY win_validation.observation_id)) AND split_part(astext(win_observations.topology), '('::text, 1) = 'POLYGON'::text;


INSERT INTO geometry_columns(f_table_catalog, f_table_schema, f_table_name, f_geometry_column, coord_dimension, srid, "type") VALUES ('', 'public', 'win_polygon_observations_view', 'topology', 2, -1, 'POLYGON');
INSERT INTO geometry_columns(f_table_catalog, f_table_schema, f_table_name, f_geometry_column, coord_dimension, srid, "type") VALUES ('', 'public', 'win_point_observations_view', 'topology', 2, -1, 'POINT');

COMMIT;